package com.ExcelServer.service.impl;


import com.ExcelServer.common.Result;
import com.ExcelServer.mapper.ExcelQueryRulesMapper;
import com.ExcelServer.pojo.entity.ExcelCreateInfo;
import com.ExcelServer.pojo.entity.ExcelQueryRules;
import com.ExcelServer.pojo.form.add.AddOneExcelQueryRule;
import com.ExcelServer.pojo.query.CountMapperQuery;
import com.ExcelServer.service.ExcelQueryRulesService;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
* @author admin
* @description 针对表【excel_query_rules】的数据库操作Service实现
* @createDate 2024-01-22 12:05:26
*/
@Service
public class ExcelQueryRulesServiceImpl extends ServiceImpl<ExcelQueryRulesMapper, ExcelQueryRules>
implements ExcelQueryRulesService {

    @Autowired
    ExcelCreateInfoServiceImpl excelCreateInfoService;

    @Autowired
    ExcelQueryRulesMapper excelQueryRulesMapper;
    @Override
    public Result countQuery(CountMapperQuery countMapperQuery) {



        Integer tableId = countMapperQuery.getTableId();

        ExcelCreateInfo byId = excelCreateInfoService.getById(tableId);
        String tableName = byId.getTableName();

        Map<String, String> queryInfo = countMapperQuery.getQueryInfo();

        QueryWrapper<ExcelQueryRules> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("table_id",tableId);
        List<ExcelQueryRules> excelQueryRules = excelQueryRulesMapper.selectList(queryWrapper);

        String sql="select * from "+tableName;
        List result = new ArrayList<>();
        if(excelQueryRules.size()>0){
            for (int i = 0; i < excelQueryRules.size(); i++) {
                ExcelQueryRules temp = excelQueryRules.get(i);
                String sort = temp.getSort();
                String groupInfo = temp.getGroupInfo();
                String selectInfo = temp.getSelectInfo();

                if(selectInfo!=null&&!selectInfo.equals("")){
                    sql = "";
                    sql+=" select "+selectInfo;
                }
                else {
                    sql+=" select * ";
                }
                if(tableName!=""&&!tableName.equals("")){
                    sql+=" from "+tableName;
                }
                String query="";
                int count = 0;
                if(queryInfo.size()>0){
                    for (Map.Entry<String, String> entry : queryInfo.entrySet()) {
                        String key = entry.getKey();
                        String value = entry.getValue();
                        count++;
                        // 处理键值对
                        if(count<queryInfo.size()){
                            query+= key +" like %"+value+"% and ";
                        }
                        else if(count==queryInfo.size()){
                            query+= "`"+key+"`" +" like '%"+value+"%' ";
                        }
//                    System.out.println("Key: " + key + ", Value: " + value);
                    }
                    sql+=" where "+query;
                }
                if(groupInfo!=null&&!groupInfo.equals("")){
                    sql+=" group by "+groupInfo;
                }
                if(sort!=null&&!sort.equals("")){
                    sql+=" order by "+sort;
                }
                sql+=";";
                List<Map<String, Object>> maps = excelQueryRulesMapper.testSql(sql);
                result.add(maps);
                System.out.println(maps);

            }
        }
        else {
            sql+=";";
            List<Map<String, Object>> maps = excelQueryRulesMapper.testSql(sql);
            result.add(maps);
        }


        // 根据统计查询规则
        /**动态sql拼接，获取查询统计结果**/
        return Result.success(result);
    }

    @Override
    public Result addCountQueryRule(AddOneExcelQueryRule addOneExcelQueryRule) {
        //校验addOneExcelQueryRule 规则
        Integer tableId = addOneExcelQueryRule.getTableId();
        ExcelCreateInfo byId = excelCreateInfoService.getById(tableId);

        String tableName = byId.getTableName();
        String selectInfo = addOneExcelQueryRule.getSelectInfo();
        String groupInfo = addOneExcelQueryRule.getGroupInfo();
        String sort = addOneExcelQueryRule.getSort();

        QueryWrapper<ExcelQueryRules> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("table_id",tableId);
        ExcelQueryRules excelQueryRules1 = excelQueryRulesMapper.selectOne(queryWrapper);
        if(excelQueryRules1!=null){
            return Result.fail("添加失败，该表已经存在统计查询规则");
        }

        if(tableName==null||tableName.equals("")){
            return Result.fail("无该表");
        }
        if(selectInfo==null||selectInfo.equals("")){
            addOneExcelQueryRule.setSelectInfo("*");
            selectInfo="*";
        }
        String sql = "select "+selectInfo+" from "+tableName;
        if(groupInfo!=null&&!groupInfo.equals("")){
            sql+=" group by "+groupInfo;
        }
        if(sort!=null&&!sort.equals("")){
            sql+=" order by "+ sort;
        }
//        String sql = "select "+selectInfo+" from "+tableName+" group by "+groupInfo+" order by "+ sort;
        //校验通过保存规则

        List<Map<String, Object>> o = excelQueryRulesMapper.testSql(sql);
        if(o.size()>0){
            ExcelQueryRules excelQueryRules = new ExcelQueryRules();
            excelQueryRules.setGroupInfo(addOneExcelQueryRule.getGroupInfo());
            excelQueryRules.setSelectInfo(addOneExcelQueryRule.getSelectInfo());
            excelQueryRules.setSort(addOneExcelQueryRule.getSort());
            excelQueryRules.setTableId(tableId);

            excelQueryRules.setNote(addOneExcelQueryRule.getNote()+sql);

            int insert = excelQueryRulesMapper.insert(excelQueryRules);
            if(insert>0){
                return Result.success("保存成功");
            }
        }
        return Result.fail("保存失败");
    }

    @Override
    public Result delCountQueryRule(String id) {
        int i = excelQueryRulesMapper.deleteById(id);
        if(i>0){
            return Result.success("删除成功");
        }
        return Result.fail("删除失败");
    }

    @Override
    public Result getCountQueryRule() {
        List<ExcelQueryRules> list = this.list();
        return Result.success(list);
    }

    @Override
    public Result updateCountQueryRule(ExcelQueryRules excelQueryRules) {
        Integer tableId = excelQueryRules.getTableId();
        ExcelCreateInfo byId = excelCreateInfoService.getById(tableId);

        String tableName = byId.getTableName();
        String selectInfo = excelQueryRules.getSelectInfo();
        String groupInfo = excelQueryRules.getGroupInfo();
        String sort = excelQueryRules.getSort();

        if(tableName==null||tableName.equals("")){
            return Result.fail("无该表");
        }
        if(selectInfo==null||selectInfo.equals("")){
            excelQueryRules.setSelectInfo("*");
            selectInfo="*";
        }
        String sql = "select "+selectInfo+" from "+tableName;
        if(groupInfo!=null&&!groupInfo.equals("")){
            sql+=" where '' group by "+groupInfo;
        }
        if(sort!=null&&!sort.equals("")){
            sql+=" order by "+ sort;
        }
        sql+=";";
        //校验通过保存规则
        List<Map<String, Object>> o;
        try {
            System.out.println(sql);
            o = excelQueryRulesMapper.testSql(sql);
        }catch (Exception e){
            e.printStackTrace();
            return Result.fail(e.getMessage());
        }

        if(o.size()>0){
            excelQueryRules.setNote(sql);
            int insert = excelQueryRulesMapper.updateById(excelQueryRules);
            if(insert>0){
                return Result.success("保存成功");
            }
        }
        return Result.fail("保存失败");
    }
}
